package com.PMSys.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.PMSys.Demain.Staff;

public class StaffDaoImp implements StaffDao {
	private Connection conn;

	public StaffDaoImp(Connection conn) {
		this.conn = conn;
	}

	/**
	 * 获取数据库里所有的客户信息
	 */
	public List<Staff> getAllStaff() {
		// 创建sql语句
		Staff staff = null;
		List<Staff> allStaff = new ArrayList<Staff>();
		String sql = "select * from Staff where s_workform != 3 order by s_id asc";
		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);

			// 执行命令
			ResultSet rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				staff = new Staff();
				staff.setS_id(rs.getString("s_id"));
				staff.setS_name(rs.getString("s_name"));
				staff.setS_sex(rs.getString("s_sex"));
				staff.setS_birthday(rs.getString("s_birthday"));
				staff.setS_idCard(rs.getString("s_idCard"));
				staff.setD_id(rs.getString("d_id"));
				staff.setP_id(rs.getString("p_id"));
				staff.setS_entrydate(rs.getString("s_entrydate"));
				staff.setS_workdate(rs.getString("s_workdate"));
				staff.setS_workform(rs.getString("s_workform"));
				staff.setS_source(rs.getString("s_source"));
				staff.setS_political(rs.getString("s_political"));
				staff.setS_phone(rs.getString("s_phone"));
				staff.setS_oldOccupation_Startdate(rs.getString("s_oldOccupation_Startdate"));
				staff.setS_oldOccupation_Enddate(rs.getString("s_oldOccupation_Enddate"));
				staff.setS_oldCompany_name(rs.getString("s_oldCompany_name"));
				staff.setS_oldWork_content(rs.getString("s_oldWork_content"));
				staff.setS_foreign_languages(rs.getString("s_foreign_languages"));
				staff.setS_kinship(rs.getString("s_kinship"));
				staff.setS_relation_name(rs.getString("s_relation_name"));
				staff.setS_relation_telephone(rs.getString("s_relation_telephone"));
				staff.setS_sexName();
				staff.setS_kinshipName();
				staff.setS_politicalName();
				staff.setS_sourceName();
				staff.setS_workformName();
				allStaff.add(staff);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allStaff;
	}

	public Staff getSelectIdStaff(Staff pstaff) {
		// 创建sql语句
		Staff staff = null;
		String sql = "select * from Staff where s_id = ? and s_workform != 3";
		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);

			// 添加参数
			pst.setString(1, pstaff.getS_id());

			// 执行命令
			ResultSet rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				staff = new Staff();
				staff.setS_id(rs.getString("s_id"));
				staff.setS_name(rs.getString("s_name"));
				staff.setS_sex(rs.getString("s_sex"));
				staff.setS_birthday(rs.getString("s_birthday"));
				staff.setS_idCard(rs.getString("s_idCard"));
				staff.setD_id(rs.getString("d_id"));
				staff.setP_id(rs.getString("p_id"));
				staff.setS_entrydate(rs.getString("s_entrydate"));
				staff.setS_workdate(rs.getString("s_workdate"));
				staff.setS_workform(rs.getString("s_workform"));
				staff.setS_source(rs.getString("s_source"));
				staff.setS_political(rs.getString("s_political"));
				staff.setS_phone(rs.getString("s_phone"));
				staff.setS_oldOccupation_Startdate(rs.getString("s_oldOccupation_Startdate"));
				staff.setS_oldOccupation_Enddate(rs.getString("s_oldOccupation_Enddate"));
				staff.setS_oldCompany_name(rs.getString("s_oldCompany_name"));
				staff.setS_oldWork_content(rs.getString("s_oldWork_content"));
				staff.setS_foreign_languages(rs.getString("s_foreign_languages"));
				staff.setS_kinship(rs.getString("s_kinship"));
				staff.setS_relation_name(rs.getString("s_relation_name"));
				staff.setS_relation_telephone(rs.getString("s_relation_telephone"));
				staff.setS_sexName();
				staff.setS_kinshipName();
				staff.setS_politicalName();
				staff.setS_sourceName();
				staff.setS_workformName();

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return staff;
	}

	public void updateStaff(Staff pstaff) {
		// 创建SQL语句
		String sql = "update Staff set s_oldOccupation_Startdate=to_date(?,'yyyy-mm-dd'),s_oldOccupation_Enddate=to_date(?,'yyyy-mm-dd'),s_oldCompany_name=?,s_oldWork_content=?,s_foreign_languages=?,s_kinship=?,s_relation_name=?,s_relation_telephone=? where s_id=?";
		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, pstaff.getS_oldOccupation_Startdate());
			pst.setString(2, pstaff.getS_oldOccupation_Enddate());
			pst.setString(3, pstaff.getS_oldCompany_name());
			pst.setString(4, pstaff.getS_oldWork_content());
			pst.setString(5, pstaff.getS_foreign_languages());
			pst.setString(6, pstaff.getS_kinship());
			pst.setString(7, pstaff.getS_relation_name());
			pst.setString(8, pstaff.getS_relation_telephone());
			pst.setString(9, pstaff.getS_id());

			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void deleteMessageStaff(Staff pstaff) {
		// 创建SQL语句
		String sql = "update Staff set s_oldOccupation_Startdate=?,s_oldOccupation_Enddate=?,s_oldCompany_name=?,s_oldWork_content=?,s_foreign_languages=?,s_kinship=?,s_relation_name=?,s_relation_telephone=? where s_id=?";
		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, null);
			pst.setString(2, null);
			pst.setString(3, null);
			pst.setString(4, null);
			pst.setString(5, null);
			pst.setString(6, null);
			pst.setString(7, null);
			pst.setString(8, null);
			pst.setString(9, pstaff.getS_id());

			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public List<Staff> getSelectConditionStaff(Staff pstaff) {
		// 创建sql语句
		Staff staff = null;
		List<Staff> allStaff = new ArrayList<Staff>();
		String sql = "select * from Staff where 1 = 1 and s_workform != 3";
		int i = 1;

		if (pstaff.getS_id() != null && pstaff.getS_id().length() != 0) {
			sql = sql + " and s_id = ?";
		}

		if (pstaff.getS_name() != null && pstaff.getS_name().length() != 0) {
			sql = sql + " and s_name like ?";
		}

		if (pstaff.getP_id() != null && pstaff.getP_id().length() != 0) {
			sql = sql + " and p_id= ?";
		}

		sql = sql + " order by s_id asc";

		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);

			if (pstaff.getS_id() != null && pstaff.getS_id().length() != 0) {
				pst.setString(i, pstaff.getS_id());
				i++;
			}

			if (pstaff.getS_name() != null && pstaff.getS_name().length() != 0) {
				pst.setString(i, pstaff.getS_name());
				i++;
			}

			if (pstaff.getP_id() != null && pstaff.getP_id().length() != 0) {
				pst.setString(i, pstaff.getP_id());
			}

			// 执行命令
			ResultSet rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				staff = new Staff();
				staff.setS_id(rs.getString("s_id"));
				staff.setS_name(rs.getString("s_name"));
				staff.setS_sex(rs.getString("s_sex"));
				staff.setS_birthday(rs.getString("s_birthday"));
				staff.setS_idCard(rs.getString("s_idCard"));
				staff.setD_id(rs.getString("d_id"));
				staff.setP_id(rs.getString("p_id"));
				staff.setS_entrydate(rs.getString("s_entrydate"));
				staff.setS_workdate(rs.getString("s_workdate"));
				staff.setS_workform(rs.getString("s_workform"));
				staff.setS_source(rs.getString("s_source"));
				staff.setS_political(rs.getString("s_political"));
				staff.setS_phone(rs.getString("s_phone"));
				staff.setS_oldOccupation_Startdate(rs.getString("s_oldOccupation_Startdate"));
				staff.setS_oldOccupation_Enddate(rs.getString("s_oldOccupation_Enddate"));
				staff.setS_oldCompany_name(rs.getString("s_oldCompany_name"));
				staff.setS_oldWork_content(rs.getString("s_oldWork_content"));
				staff.setS_foreign_languages(rs.getString("s_foreign_languages"));
				staff.setS_kinship(rs.getString("s_kinship"));
				staff.setS_relation_name(rs.getString("s_relation_name"));
				staff.setS_relation_telephone(rs.getString("s_relation_telephone"));
				staff.setS_sexName();
				staff.setS_kinshipName();
				staff.setS_politicalName();
				staff.setS_sourceName();
				staff.setS_workformName();
				allStaff.add(staff);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allStaff;
	}

	public List<Staff> getSelectStaffForDepartmentId(Staff pStaff) {
		// 创建sql语句
		List<Staff> allStaff = new ArrayList<Staff>();
		Staff staff = null;
		String sql = "select * from Staff where d_id = ? and s_workform != 3 order by s_id asc";
		// 创建命令对象
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);

			// 添加参数
			pst.setString(1, pStaff.getD_id());

			// 执行命令
			ResultSet rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				staff = new Staff();
				staff.setS_id(rs.getString("s_id"));
				staff.setS_name(rs.getString("s_name"));
				staff.setS_sex(rs.getString("s_sex"));
				staff.setS_birthday(rs.getString("s_birthday"));
				staff.setS_idCard(rs.getString("s_idCard"));
				staff.setD_id(rs.getString("d_id"));
				staff.setP_id(rs.getString("p_id"));
				staff.setS_entrydate(rs.getString("s_entrydate"));
				staff.setS_workdate(rs.getString("s_workdate"));
				staff.setS_workform(rs.getString("s_workform"));
				staff.setS_source(rs.getString("s_source"));
				staff.setS_political(rs.getString("s_political"));
				staff.setS_phone(rs.getString("s_phone"));
				staff.setS_oldOccupation_Startdate(rs.getString("s_oldOccupation_Startdate"));
				staff.setS_oldOccupation_Enddate(rs.getString("s_oldOccupation_Enddate"));
				staff.setS_oldCompany_name(rs.getString("s_oldCompany_name"));
				staff.setS_oldWork_content(rs.getString("s_oldWork_content"));
				staff.setS_foreign_languages(rs.getString("s_foreign_languages"));
				staff.setS_kinship(rs.getString("s_kinship"));
				staff.setS_relation_name(rs.getString("s_relation_name"));
				staff.setS_relation_telephone(rs.getString("s_relation_telephone"));
				staff.setS_sexName();
				staff.setS_kinshipName();
				staff.setS_politicalName();
				staff.setS_sourceName();
				staff.setS_workformName();
				allStaff.add(staff);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allStaff;
	}
}
